﻿Imports System
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Collections
Imports System.IO

Public Class BaseDB
    Public Shared sqlConnect As SqlConnection

    Public Shared Function GetConnectStrFromFile(ByVal pFileSpec As String) As String
        Dim mPassFromFile As String = ""
        Dim mCnnStr As String = ""
        Try
            Dim sr As StreamReader = New StreamReader(pFileSpec)
            Dim line As String
            Do
                line = sr.ReadLine()
                If Not IsNothing(line) Then
                    Dim str As String = "Provider=SQLOLEDB.1;"
                    Dim index = line.IndexOf(str) + str.Length()
                    If index = str.Length() Then
                        mCnnStr = line.Substring(index)
                        Exit Do
                    End If
                End If
            Loop Until line Is Nothing
            sr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        'mCnnStr &= ";Pooling = true; Min Pool Size = 1; Max Pool Size = 50"
        Return mCnnStr
    End Function

    Public Shared Sub GanCauLenhKetNoi(ByRef strConnect As String, ByVal sUserName As String, ByVal sPassword As String)
        ThayTheGiaTri(strConnect, "User ID", sUserName)
        ThayTheGiaTri(strConnect, "Password", sPassword)
    End Sub
    'ham dung de thay gia tri cua mot thanh phan trong chuoi
    Public Shared Sub ThayTheGiaTri(ByRef strConnect As String, ByVal sThanhPhan As String, ByVal sGiaTri As String)
        Dim sThayThe As String = sThanhPhan & "=" & sGiaTri
        Dim viTriDau As Integer = strConnect.IndexOf(sThanhPhan)
        'neu tim thay
        If (viTriDau <> -1) Then
            'delete het cho den khi gap ";" hoac cuoi chuoi
            'tim vi tri cuoi cua chuoi nay

            Dim i As Integer = 0
            While (strConnect(viTriDau + i) <> ";" And viTriDau + i < strConnect.Length)
                i += 1
            End While
            'delete
            strConnect = strConnect.Remove(viTriDau, i)
            'thay the bang chuoi moi
            strConnect = strConnect.Insert(viTriDau, sThayThe)
        Else
            sThayThe = ";" & sThayThe
            strConnect = String.Concat(strConnect, sThayThe)
        End If
    End Sub

    Public Shared Sub OpenConnect(Optional ByVal dbName As String = "")
        Try
            'Mo ket noi CSDL
            sqlConnect = New SqlConnection
            If connectString = "" Then
                connectString = GetConnectStrFromFile("DBConnect.udl")
            End If
            sqlConnect.ConnectionString = connectString

            'Neu ket noi voi dbName khac
            If dbName <> "" Then
                Dim sConnectString As String = sqlConnect.ConnectionString()
                Dim indexStart, indexEnd As Integer
                indexStart = sConnectString.IndexOf("Initial Catalog=") + 16
                indexEnd = sConnectString.IndexOf(";Data Source")
                Dim strReplace = sConnectString.Substring(indexStart, indexEnd - indexStart)
                sqlConnect.ConnectionString = sConnectString.Replace(strReplace, dbName)
            End If

            GanCauLenhKetNoi(sqlConnect.ConnectionString, strLoginName_SA, strPassword_SA)
            If sqlConnect.State = ConnectionState.Closed Then
                sqlConnect.Open()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Shared Sub CloseConnect()
        Try
            If sqlConnect.State = ConnectionState.Open Then
                sqlConnect.Close()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    'Khoi tao SqlCommand 1
    Public Shared Function InitializeConnect_Command(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlCommand
        'Thuc thi store procedure
        Dim com As New SqlCommand(strProcedureName, sqlConnect)
        OpenConnect()
        com.Connection = sqlConnect
        com.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To UBound(par)
            com.Parameters.Add(par(i))
        Next
        Return com
    End Function

    'Khoi tao SqlCommand 2
    Public Shared Function InitializeConnect_Command(ByVal strProcedureName As String) As SqlCommand
        'Thuc thi store procedure
        Dim com As New SqlCommand(strProcedureName)
        OpenConnect()
        com.Connection = sqlConnect
        com.CommandType = CommandType.StoredProcedure
        Return com
    End Function

    'Ham thuc thi nhung procedure kiem tra
    Public Shared Function ExecSqlProcedure_Command(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlCommand
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName, par)
        Dim lRowsAffected As Long = 0
        Try
            com.ExecuteReader()
        Catch ex As Exception
            Throw ex
        End Try
        Return com
    End Function
    'Ham thuc thi pro nhung bo qua expride timeout, ko bat ex
    Public Shared Function ExecSqlProcedure_Command(ByVal strProcedureName As String) As SqlCommand
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName)
        Dim lRowsAffected As Long = 0
        Try
            com.ExecuteReader()
        Catch ex As Exception
            'Throw ex
        End Try
        Return com
    End Function

    'Ham thuc thi nhung procedure insert, update, delete 1
    Public Shared Function ExecSqlProcedure_NoneQuery(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As Long
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName, par)
        Dim lRowsAffected As Long = 0
        Try
            com.Transaction = com.Connection.BeginTransaction()
            lRowsAffected = com.ExecuteNonQuery()
            com.Transaction.Commit()
        Catch ex As Exception
            com.Transaction.Rollback()
            Throw ex
        Finally
            com.Connection.Close()
        End Try
        Return lRowsAffected
    End Function

    Public Shared Function ExecSql_NoneQuery(ByVal strSql As String) As Long
        Dim com As SqlCommand
        com = New SqlCommand
        Dim lRowsAffected As Long = 0
        Try
            com.CommandText = strSql
            OpenConnect()
            com.Connection = sqlConnect
            com.Transaction = com.Connection.BeginTransaction()
            lRowsAffected = com.ExecuteNonQuery()
            com.Transaction.Commit()
            com.Connection.Close()
        Catch ex As Exception
            com.Transaction.Rollback()
            Throw ex
        End Try
        Return lRowsAffected
    End Function

    'Thuc thi nhung query procedure 1
    Public Shared Function ExecSqlProcedure_DataSet(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As DataSet
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName, par)
        Dim myAdapter As New SqlDataAdapter(com)
        Dim ds As New DataSet
        myAdapter.Fill(ds, strProcedureName)
        com.Parameters.Clear()
        com.Connection.Close()
        Return ds
    End Function

    'Thuc thi nhung query procedure 2
    Public Shared Function ExecSqlProcedure_DataSet(ByVal strProcedureName As String) As DataSet
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName)
        Dim myAdapter As New SqlDataAdapter(com)
        Dim ds As New DataSet
        Try
            myAdapter.Fill(ds, strProcedureName)
        Catch ex As Exception
            Throw ex
        Finally
            com.Connection.Close()
        End Try
        Return ds
    End Function

    Public Shared Function ExecSqlProcedure_DataReader(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlDataReader
        Dim Result As Boolean = False
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName, par)
        Dim myReader As SqlDataReader
        Try
            myReader = com.ExecuteReader()
        Catch ex As Exception
            Throw ex
        End Try
        Return myReader
    End Function

    Public Shared Function ExecSql_DataReader(ByVal strSql As String) As SqlDataReader
        Dim com As New SqlCommand
        com.CommandText = strSql
        OpenConnect()
        com.Connection = sqlConnect
        Dim myReader As SqlDataReader
        Try
            myReader = com.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            Throw ex
        End Try
        Return myReader
    End Function

    ''strSql la dang cau lenh nhu SELECT COUNT(*) FROM
    'Public Shared Function KiemTraTonTai(ByVal strSql As String) As Boolean
    '    Dim result As Object = ExecSql_DataValue(strSql)
    '    If result > 0 Then
    '        Return True
    '    End If
    '    Return False
    'End Function

    'Public Shared Function KiemTraTonTai(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As Boolean
    '    Dim result As Object = ExecSqlProcedure_DataValue(strProcedureName, par)
    '    If Not result Is Nothing Then
    '        Return True
    '    End If
    '    Return False
    'End Function

    Public Shared Function ExecSql_DataSet(ByVal strSql As String) As DataSet
        Dim com As New SqlCommand
        Dim ds As New DataSet
        Try
            com.CommandText = strSql
            OpenConnect()
            com.Connection = sqlConnect
            Dim da As New SqlDataAdapter
            da.SelectCommand = com
            da.Fill(ds)
        Catch ex As Exception
            Throw ex
        Finally
            com.Connection.Close()
        End Try
        Return ds
    End Function

    Public Shared Function ExecSql_DataValue(ByVal strSql As String) As Object
        Dim com As New SqlCommand
        com.CommandText = strSql
        OpenConnect()
        com.Connection = sqlConnect
        Dim result As Object
        Try
            result = com.ExecuteScalar()
        Catch ex As Exception
            Throw ex
        Finally
            com.Connection.Close()
        End Try
        Return result
    End Function

    Public Shared Function ExecSqlProcedure_DataValue(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As Object
        Dim com As SqlCommand
        com = InitializeConnect_Command(strProcedureName, par)
        Dim result As Object
        Try
            result = com.ExecuteScalar()
        Catch ex As Exception
            Throw ex
        Finally
            com.Connection.Close()
        End Try
        Return result
    End Function

    Public Shared Function GetObjectValue(ByVal value As Object) As Object
        If value Is DBNull.Value Then
            Return Nothing
        Else
            Return value
        End If
    End Function
End Class